<?php
class Setting extends Admin_Controller
{
	
	function __construct()
	{
		parent::__construct();
		$this->load->model('setting_model','setting');
		$this->load->model('agency_detail_model','agency_detail');
		$this->load->model('voucher/status_model','status');
	}
	
	function index($name)
	{
		
		$data['result'] = $this->db->getArray('select * from settings where name = \''.$name.'\' order by fund_id');
		dbConvert($data['result']);
		$this->template->build('setting_index',$data);
	}
	
	function expire($fund_id)
	{
		$this->load->model('voucher/fund_model','fund');
		$data['fund'] = $this->fund->get_row($fund_id);
		$data['rs'] = $this->db->getRow('select * from settings where name = \'expire\' and fund_id = '.$fund_id);
		dbConvert($data['rs']);
		$this->template->build('setting_expire',$data);
	}
	
	function save()
	{
		if($_POST)
		{
			add_log('แก้ไขกำหนดวันส่งข้อมูล');
			foreach($_POST['value'] as $key => $value)
			{
				$data['id'] = $key;
				$data['value'] = $value;
				$this->setting->save($data);
				unset($data);
			}
			set_notify('success', lang('save_data_complete'));
		}
		redirect($_SERVER['HTTP_REFERER']);
	}
	
	function dashboard($fund_id = 1)
	{
		//$this->db->debug = true;
		$this->load->model('voucher/fund_model','fund');
		$data['status'] = array(
		'0'=>'ไม่มีข้อมูล',
		'1'=>'รอการตรวจสอบ',
		'2'=>'<span class="green">ผ่านการตรวจสอบ</span>',
		'3'=>'<span class="red">กลับไปแก้ไข</span>'
		);
		$data['fund'] = $this->fund->get_row($fund_id);
		if(level_data('view'))
		//if(level_data('view') and $fund_id == 1) // แก้ไขสำหรับ กองทุนเด็กและปราบปราม 2/4/2012
		{
		
		
		$data['fund_id'] = $fund_id;

		list($year,$month,$data['year'],$data['month']) = last_voucher_date($fund_id);

		//$this->db->debug = TRUE;
		$data['status_1'] = $this->db->getone('select count(id)
					from status 
					where status in(1)  
					and fund_id = '.$fund_id.'
					and status_year = '.$year.'
					and status_month = '.$month
		); 
		
		$data['status_2'] = $this->db->getone('select count(id)
					from status 
					where status in(2)  
					and fund_id = '.$fund_id.'
					and status_year = '.$year.'
					and status_month = '.$month
		);
		
		$data['status_3'] = $this->db->getone('select count(id)
					from status 
					where status in(3)  
					and fund_id = '.$fund_id.'
					and status_year = '.$year.'
					and status_month = '.$month
		);
		
		if($fund_id=='1')
		{
			$where = 'where agencies.id not in (1,79)';
		}
		else
		{
			$where = 'where agencies.id in (2)';
		}
		
		$data['result'] = $this->db->getArray('
		select agencies.*
		,status.id status_id
		,COALESCE(status.status,0) status
		,COALESCE(agency_details.is_lock,1) is_lock
		from agencies  
		left join 	(
					select * 
					from status 
					where status.fund_id = '.$fund_id.'
					and status.status_year = '.$year.'
					and status.status_month = '.$month.'
					) status 
		on status.agency_id = agencies.id
		left join 	(
					select * 
					from agency_details
					where agency_details.fund_id = '.$fund_id.'
					) agency_details
		on agency_details.agency_id = agencies.id
		'.$where.'
		
		');
		//$this->db->debug = FALSE;
		dbConvert($data['result']);
		$this->template->build('setting_dashboard',$data);
		}
		else
		{   
			$this->load->model('user/agency_model','agency');
			$user = login_data();
			$data['agency'] = $this->agency->get_row($user['agency_id']);
			$data['result'] = $this->status->where('fund_id = '.$fund_id.' and agency_id = '.$user['agency_id'])->get();
			$data['pagination'] = $this->status->pagination();
			$this->template->build('setting_dashboard_view',$data);
		}
	}
	
	function approve($id)
	{
		$this->load->model('voucher/fund_model','fund');
		$this->load->model('voucher/voucher_model','voucher');
		$this->load->model('voucher/voucher_detail_model','voucher_detail');
		
		$data['id'] = $id;
		$data['status'] = '2';
		$this->status->save($data);
		//$this->db->debug = TRUE;
		//********** Create Balance Voucher ***************/
		if($status['status_month']<>'9')
		{
			$status = $this->status->get_row($id);
			
			$prev_balances = $this->voucher->get_balance($status['fund_id'],$status['agency_id'],$status['status_year'],$status['status_month']);
			pre_dump($prev_balances);
			if($prev_balances)
			{
				
				$voucher['description'] = 'ยอดยกมาประจำเดือน '.month($status['status_month'] + 1).' ปี '.($status['status_year'] + 543);
				$voucher['voucher_type_id'] = 3;
				$voucher['user_id'] = $this->session->userdata('id');
				$voucher['fund_id'] = $status['fund_id'];
				$voucher['is_balance'] = 1;
				$voucher['voucher_date'] = $status['status_year'].'-'.($status['status_month'] + 1).'-1';
				$voucher['agency_id'] = $status['agency_id'];
				
				$approve = $this->db->getRow('select * from approves where fund_id = '.$status['fund_id'].' and agency_id = '.$status['agency_id']);
				dbConvert($approve);
				$voucher['examiner'] = $approve['examiner'];
				$voucher['examiner_position'] = $approve['examiner_position'];
				$voucher['endorsee'] = $approve['endorsee'];
				$voucher['endorsee_position'] = $approve['endorsee_position'];
				
				$balance_id = $this->voucher->save($voucher);
				foreach($prev_balances as $balance)
				{
					if(($balance['dr_sum']<>"0.00")||($balance['cr_sum']<>"0.00"))
					{
						$detail['account_id'] = $balance['id'];
						$detail['price'] = ($balance['dr_sum']<>"0.00")?$balance['dr_sum']:$balance['cr_sum'];
						$detail['type'] = ($balance['dr_sum']<>"0.00")?'dr':'cr';
						$detail['voucher_id'] = $balance_id;
						$this->voucher_detail->save($detail);
					}
						unset($detail);
				}
				unset($voucher);
			}
		}
		/*
		if($status['status_month']=='9')
		{
			echo 'test';
			//Modules::run('voucher/close/',$status['fund_id'],$status['year'],$status['agency_id'],$this->session->userdata('id'));
			$data['fund'] = $this->fund->get_row($status['fund_id']);
			//$user = login_data();
			$fund_id = $status['fund_id'];
			$agency_id = $status['agency_id'];
			$user_id = $status['user_id'];
			$year = $status['status_year'];
			list($start_date,$end_date) = year_budget($year);
			$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
			$where .= ' and vouchers.fund_id = '.$fund_id.' and vouchers.agency_id = '.$agency_id;
			
			$data['earning'] = $this->voucher_detail->get('
			select accounts.id,accounts.name,accounts.category_id,price from accounts
			join 
			(select account_id,sum(price) price from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null)  group by account_id) b
			on b.account_id = accounts.id
			where accounts.category_id = 4
			and accounts.fund_id = '.$fund_id.'
			',FALSE);
			
			$data['cost'] = $this->voucher_detail->get('
			select accounts.id,accounts.name,accounts.category_id,price from accounts
			join 
			(select account_id,sum(price) price from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null)  group by account_id) b
			on b.account_id = accounts.id
			where accounts.category_id = 5
			and accounts.fund_id = '.$fund_id.'
		
			',FALSE);
			
			$sum_earning = $this->db->getOne('
			select sum(price) price from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on voucher_details.account_id = accounts.id  where "TYPE" = \'cr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) 
			and accounts.category_id = 4
			and vouchers.fund_id = '.$fund_id.'
			',FALSE);
			
			$sum_cost = $this->db->getOne('
			select sum(price) price from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on voucher_details.account_id = accounts.id  where "TYPE" = \'dr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) 
			and accounts.category_id = 5
			and vouchers.fund_id = '.$fund_id.'
			',FALSE);
			
			$sum = $sum_earning - $sum_cost;
			echo $sum;
		
			if($sum <> 0)
			{
				$voucher['voucher_type_id'] = 3;
				$voucher['user_id'] = $user_id;
				$voucher['fund_id'] = $fund_id;
				$voucher['voucher_date'] = $end_date;
				$voucher['agency_id'] = $agency_id;
				$voucher['mode'] = 'closed';
				$voucher['approve_name'] = $approve_name;
				$voucher['description'] = 'ปิดบัญชีรายได้และค่าใช้จ่ายเข้าบัญชีรายได้สูง/(ต่ำ) กว่าค่าใช้จ่าย';
				$balance_id = $this->voucher->save($voucher);
				foreach($data['earning'] as $dr)
				{
					$detail['account_id'] = $dr['id'];
					$detail['price'] = $dr['price'];
					$detail['type'] = 'dr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
				}
				if($sum < 0)
				{
					$detail['account_id'] = 13;
					$detail['price'] = $sum * -1;
					$detail['type'] = 'dr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
				}
				foreach($data['cost'] as $cr)
				{
					$detail['account_id'] = $cr['id'];
					$detail['price'] = $cr['price'];
					$detail['type'] = 'cr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
				}
				if($sum > 0)
				{
					$detail['account_id'] = 13;
					$detail['price'] = $sum;
					$detail['type'] = 'cr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
				}
				
				$voucher['voucher_type_id'] = 3;
				$voucher['user_id'] = $user_id;
				$voucher['fund_id'] = $fund_id;
				$voucher['voucher_date'] = $end_date;
				$voucher['agency_id'] = $agency_id;
				$voucher['mode'] = 'depreciation';
				$voucher['approve_name'] = $approve_name;
				$voucher['description'] = 'ปิดบัญชีรายได้สูง/(ต่ำ) กว่าค่าใช้จ่ายเข้าบัญชีรายได้สูง/(ต่ำ)กว่าค่าใช้จ่ายสะสม';
				$balance_id = $this->voucher->save($voucher);
				if($sum > 0)
				{
					$detail['account_id'] = 13;
					$detail['price'] = $sum;
					$detail['type'] = 'dr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
					
					$detail['account_id'] = 14;
					$detail['price'] = $sum;
					$detail['type'] = 'cr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
				}
				if($sum < 0)
				{
					$detail['account_id'] = 14;
					$detail['price'] = $sum * -1;
					$detail['type'] = 'dr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
					
					$detail['account_id'] = 13;
					$detail['price'] = $sum * -1;
					$detail['type'] = 'cr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
					unset($detail);
				}
			}
		}*/
		redirect($_SERVER['HTTP_REFERER']);
	}
	
	function fail($id)
	{
		//$this->db->debug = TRUE;
		/*$this->load->model('voucher/voucher_model','voucher');
		$this->load->model('voucher/voucher_detail_model','voucher_detail');
		
		
		
		$voucher_id = $this->db->getOne('select id 
		from vouchers 
		where YEAR(vouchers.voucher_date) = '.$data['status_year'].' 
		and MONTH(vouchers.voucher_date) = '.($data['status_month']+1).' 
		and vouchers.is_balance = 1 
		and vouchers.agency_id = '.$data['agency_id']);

		$this->voucher_detail->delete('voucher_id',$voucher_id);
		$this->voucher->delete($voucher_id);
		*/
		$data = $this->status->get_row($id);
		$data['status'] = '3';
		$this->status->save($data);
		redirect($_SERVER['HTTP_REFERER']);
	}
	
	function toggle($fund_id,$agency_id)
	{
		//$this->db->debug = TRUE;
		$data = $this->db->getRow('select * from agency_details where fund_id = ? and agency_id = ?',array($fund_id,$agency_id));
		dbConvert($data);
		//pre_dump($data);
		if($data)
		{
			$data['is_lock'] = ($data['is_lock']=='1')?'0':'1';
		}
		else
		{
			$data['fund_id'] = $fund_id;
			$data['agency_id'] = $agency_id;
			$data['is_lock'] = '0';
		}
		
		$this->agency_detail->save($data);
		
		if($data['is_lock']==0)
		{
			set_notify('success','ปลดล็อคเรียบร้อยแล้วค่ะ');
		}
		else
		{
			set_notify('success','ล็อคเรียบร้อยแล้วค่ะ');
		}
		redirect($_SERVER['HTTP_REFERER']);
	}
	
}
?>